﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using Common;
using Entities;

namespace Dao
{
    /// <summary>
    /// Provides a way of query with Replies from a SQL Server Database
    /// </summary>
    public class RepliesData
    {
        #region Variables private
        private IList<Reply> _listReply = null;
        private Reply _reply = null; 
        #endregion

        #region Methods public
        /// <summary>
        /// Get all replies by post id
        /// </summary>
        /// <returns>List all replies by Id</returns>
        public IList<Reply> GetAllByPostId(int postId)
        {
            SqlConnection conn = new SqlConnection(SQLDBHelper.CONN_STRING);
            try
            {
                SqlParameter[] parm = new SqlParameter[] { new SqlParameter("@postId", SqlDbType.Int, 2) };
                parm[0].Value = postId;
                SqlDataReader reader = SQLDBHelper.ExecuteReader(conn, CommandType.StoredProcedure, "sp_Reply_GetAllByPostId", parm);
                while (reader.Read())
                {
                    _reply = new Reply();
                    if (!reader.IsDBNull(0))
                        _reply.ReplyID = reader.GetInt32(0);
                    if (!reader.IsDBNull(1))
                        _reply.PostID = reader.GetInt32(1);
                    if (!reader.IsDBNull(2))
                        _reply.UserID = reader.GetInt32(2);
                    if (!reader.IsDBNull(3))
                        _reply.ReplyDate = reader.GetDateTime(3);
                    if (!reader.IsDBNull(4))
                        _reply.Body = reader.GetString(4);
                    if (!reader.IsDBNull(5))
                        _reply.Status = reader.GetBoolean(5);
                    _listReply.Add(_reply);
                }
            }
            catch (Exception ex)
            {
                ErrorManagement.TrackLog(ex.Message);
                throw ex;
            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                    conn.Close();
            }
            return _listReply;
        }
        /// <summary>
        /// Get all replies
        /// </summary>
        /// <returns>List of replies</returns>
        public IList<Reply> GetAll()
        {
            SqlConnection conn = new SqlConnection(SQLDBHelper.CONN_STRING);
            _listReply = new List<Reply>();
            try
            {
                SqlDataReader reader = SQLDBHelper.ExecuteReader(conn, CommandType.StoredProcedure, "sp_Reply_GetAll", null);
                while (reader.Read())
                {
                    _reply = new Reply();
                    if (!reader.IsDBNull(0))
                        _reply.ReplyID = reader.GetInt32(0);
                    if (!reader.IsDBNull(1))
                        _reply.PostID = reader.GetInt32(1);
                    if (!reader.IsDBNull(2))
                        _reply.UserID = reader.GetInt32(2);
                    if (!reader.IsDBNull(3))
                        _reply.ReplyDate = reader.GetDateTime(3);
                    if (!reader.IsDBNull(4))
                        _reply.Body = reader.GetString(4);
                    if (!reader.IsDBNull(5))
                        _reply.Status = reader.GetBoolean(5);
                    _listReply.Add(_reply);
                }
            }
            catch (Exception ex)
            {
                ErrorManagement.TrackLog(ex.Message);
                throw ex;
            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                    conn.Close();
            }
            return _listReply;
        }
        /// <summary>
        /// Insert or Update a reply
        /// If ReplyID = 0 then Insert a reply
        /// If ReplyID != 0 then Update a reply
        /// </summary>
        /// <param name="reply">Object of reply</param>
        /// <returns>True is successful, False is unsuccessful</returns>
        public bool InsertOrUpdate(Reply reply)
        {
            SqlConnection conn = new SqlConnection(SQLDBHelper.CONN_STRING);
            int result = 0;
            try
            {
                SqlParameter[] parms = new SqlParameter[] { 
                                new SqlParameter("@ReplyID", SqlDbType.Int),
                                new SqlParameter("@PostID", SqlDbType.Int),
                                new SqlParameter("@UserID", SqlDbType.Int),
                                new SqlParameter("@ReplyDate", SqlDbType.DateTime),
                                new SqlParameter("@Body", SqlDbType.NText),
                                new SqlParameter("@Status", SqlDbType.Bit)};

                parms[0].Value = reply.ReplyID;
                parms[1].Value = reply.PostID;
                parms[2].Value = reply.UserID;
                parms[3].Value = reply.ReplyDate;
                parms[4].Value = reply.Body;
                parms[5].Value = reply.Status;

                if (reply.ReplyID == 0)
                    result = SQLDBHelper.ExecuteNonQuery(conn, CommandType.StoredProcedure, "sp_Replies_Insert", parms);
                else
                    result = SQLDBHelper.ExecuteNonQuery(conn, CommandType.StoredProcedure, "sp_Replies_Update", parms);
            }
            catch (Exception ex)
            {
                ErrorManagement.TrackLog(ex.Message);
                throw ex;
            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                    conn.Close();
            }
            return result > 0 ? true : false;
        }
        /// <summary>
        /// Delete a reply
        /// </summary>
        /// <param name="replyID">ID of reply want to delete</param>
        /// <returns>True is successful, False is unsuccessful</returns>
        public bool DeleteByID(int replyID)
        {
            SqlConnection conn = new SqlConnection(SQLDBHelper.CONN_STRING);
            int result = 0;
            try
            {
                SqlParameter[] parm = new SqlParameter[] { new SqlParameter("@ReplyID", SqlDbType.Int) };
                parm[0].Value = replyID;
                result = SQLDBHelper.ExecuteNonQuery(conn, CommandType.StoredProcedure, "sp_Reply_DeleteByID", parm);
            }
            catch (Exception ex)
            {
                ErrorManagement.TrackLog(ex.Message);
                throw ex;
            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                    conn.Close();
            }
            return result > 0 ? true : false;
        } 
        #endregion
    }
}
